package com.artfii.fluentsql.core.jdbc;

import com.artfii.fluentsql.core.QueryKit;
import com.artfii.fluentsql.core.TableFieldSpell;
import com.artfii.fluentsql.core.exception.FluentSqlException;
import com.artfii.fluentsql.core.exception.SqlHackException;
import com.artfii.fluentsql.core.tools.BeanMapUtils;
import com.artfii.fluentsql.core.tools.ObjFactory;
import com.artfii.fluentsql.core.tools.PrintUtil;
import com.artfii.fluentsql.core.tools.StringKit;
import com.artfii.fluentsql.core.tools.cache.Cache;
import com.artfii.fluentsql.core.tools.cache.CacheManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicLong;
import java.util.stream.Collectors;

/**
 * Func:   : 实现默认的 jdbc 功能
 *
 * @author : Lqf(leeton)
 * @date : 2021/4/28.
 */
public class FluentSqlQuery {
    private final static Logger logger = LoggerFactory.getLogger(FluentSqlQuery.class);

    private FluentConnection fluentConnection;
    private boolean check_sql_hack = FluentSqlConfig.default_config.getSql_hack_check();

    public FluentSqlQuery() {
    }

    public FluentSqlQuery(FluentConnection fluentConnection) {
        this.fluentConnection = fluentConnection;
    }

    //=============== JDBC STATEMENT  ===============================

    private Integer write(final String namedSql, Map<String, Object> paramMap) {
        Integer sqlKey = namedSql.hashCode();
        List<ParamInfo> paramInfoList = buildParamInfoAndChangeNamedSql(sqlKey, namedSql, paramMap);
        String symbolsql = getSqlFromCache(sqlKey);
        return write(symbolsql, paramInfoList);
    }

    private Integer write(final String symbolsql, Object[] paramArray) {
        List<ParamInfo> paramInfoList = buildParamInfo(symbolsql, paramArray);
        return write(symbolsql, paramInfoList);
    }

    private Integer write(final String finalSql, List<ParamInfo> paramInfoList) {
        if(null == fluentConnection || fluentConnection.isClosed()){
            fluentConnection = ConnectionBuilder.getConnectionOfPool(false);
        }
        final PreparedStatement pstmt = buildPstmt(finalSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        try {
            if (null != paramInfoList && paramInfoList.size() > 0) {
                for (ParamInfo p : paramInfoList) {
                    addParameter(pstmt, p.namedIdx, p.vol);
                }
            }
            return pstmt.executeUpdate();
        } catch (Exception e) {
            throw new FluentSqlException(e);
        } finally {
            closeStatement(pstmt);
        }
    }

    /**
     * 批处理写入数据
     *
     * @param updateSql 带点位符(?)的SQL,如果是named方式请先做好格式转换
     * @param paramList List<Map<idx,value>>
     */
    private Integer batchWrite(String updateSql, List<Map<Integer, Object>> paramList) {
        if(null == fluentConnection || fluentConnection.isClosed()){
            fluentConnection = ConnectionBuilder.getConnectionOfPool(false);
        }
        try {
           boolean sup = fluentConnection.getMetaData().supportsBatchUpdates();
//           logger.info("supportsBatchUpdates:{}",sup);
        } catch (SQLException e) {
            throw new FluentSqlException("database not support batch updates!");
        }
        final PreparedStatement pstmt = buildPstmt(updateSql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        try {
            for (Map<Integer, Object> valMap : paramList) {
                for (Integer key : valMap.keySet()) {//一组入参
                    addParameter(pstmt, key, valMap.get(key));
                }
                //加入批处理
                pstmt.addBatch();
            }
            return pstmt.executeBatch().length;
        } catch (Exception e) {
            closeStatement(pstmt);
            throw new FluentSqlException(e);
        } finally {
            closeStatement(pstmt);
        }
    }

    private List<Map<String, Object>> read(String namedSql, Map<String, Object> params) {
        Integer sqlKey = namedSql.hashCode();
        List<ParamInfo> paramInfoList = buildParamInfoAndChangeNamedSql(sqlKey, namedSql, params);
        String finalSql = getSqlFromCache(sqlKey);
        return read(finalSql, paramInfoList);
    }

    private List<Map<String, Object>> read(String symbolSql, Object[] paramArray) {
        List<ParamInfo> paramInfoList = buildParamInfo(symbolSql, paramArray);
        return read(symbolSql, paramInfoList);
    }

    private List<Map<String, Object>> read(String smbolSql, List<ParamInfo> paramInfoList) {
        if(null == fluentConnection || fluentConnection.isClosed()){
            fluentConnection = ConnectionBuilder.getConnectionOfPool(true);
        }
        // build pstmt
        final PreparedStatement pstmt = buildPstmt(smbolSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        //set params
        if (null != paramInfoList && paramInfoList.size() > 0) {
            for (ParamInfo p : paramInfoList) {
                addParameter(pstmt, p.namedIdx, p.vol);
            }
        }
        //qurey
        return query(pstmt);
    }

    private List<Map<String, Object>> query(PreparedStatement pstmt) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try {
            ResultSet r = pstmt.executeQuery();
            ResultSetMetaData rsm = r.getMetaData();
            if (rsm.getColumnCount() > 0) {
                resultList = new ArrayList<>(pstmt.getMaxRows());
                while (r.next()) {
                    int cnums = rsm.getColumnCount();
                    Map<String, Object> rsMap = new HashMap<>(cnums);
                    while (cnums > 0) {
                        rsMap.put(rsm.getColumnName(cnums), r.getObject(cnums));
                        cnums--;
                    }
                    resultList.add(rsMap);
                }
            }
            r.close();
            return resultList;
        } catch (SQLException e) {
            closeStatement(pstmt);
            throw new FluentSqlException(e);
        } finally {
            closeStatement(pstmt);
        }
    }

//=============================== CRUD ===============================

    public Integer execute(String sql) {
        Integer result = write(sql, new Object[]{});
        return result;
    }

    public Integer execute(String sql, Object[] paramArray) {
        Integer result = write(sql, paramArray);
        return result;
    }

    public Integer execute(String sql, Map<String, Object> paramMap) {
        Integer result = write(sql, paramMap);
        return result;
    }

    public Integer batchSave(String sql, List<Map<Integer, Object>> paramList) {
        Integer result = batchWrite(sql, paramList);
        return result;
    }

    public Integer delete(String sql) {
        return execute(sql, new Object[]{});
    }

    public Integer delete(String sql, Object[] paramArray) {
        return execute(sql, paramArray);
    }

    public Integer delete(String sql, Map<String, Object> paramMap) {
        return execute(sql, paramMap);
    }

    public <T> List<T> getList(String sql, Object[] paramArray, Class<T> resultClass) {
        return getListVal(read(sql, paramArray), resultClass);
    }

    public <T> List<T> getList(String sql, Map<String, Object> paramMap, Class<T> resultClass) {
        return getListVal(read(sql, paramMap), resultClass);
    }

    public <T> T getObject(String sql, Object[] paramArray, Class<T> resultClass) {
        return getObjVal(read(sql, paramArray), resultClass);
    }

    public <T> T getObject(String sql, Map<String, Object> params, Class<T> resultClass) {
        return getObjVal(read(sql, params), resultClass);
    }

    //======================================PRIVATE METHODS ======================================================

    /**
     * 设置入参到 PreparedStatement ,这里不考虑入参为数组或列表的情况,IN()语句的数组，在上层做串接处理
     */
    private void addParameter(PreparedStatement pstmt, Integer paramIdx, Object value) {
        // must cover most of types: BigDecimal,Boolean,SmallInt,Double,Float,byte[]
        try {
            if (null == value) {
                pstmt.setObject(paramIdx, value);
                return;
            }
            Class parameterClass = value.getClass();
            if (InputStream.class.isAssignableFrom(parameterClass)) {
                pstmt.setBinaryStream(paramIdx, (InputStream) value);
            } else if (Integer.class == parameterClass) {
                pstmt.setInt(paramIdx, (Integer) value);
            } else if (Long.class == parameterClass) {
                pstmt.setLong(paramIdx, (Long) value);
            } else if (String.class == parameterClass || parameterClass.isAssignableFrom(Character.class)) {
                pstmt.setString(paramIdx, (String) value);
            } else if (Timestamp.class == parameterClass) {
                pstmt.setObject(paramIdx, value);
            } else if (Time.class == parameterClass) {
                pstmt.setObject(paramIdx, value);
            } else if (Date.class == parameterClass) {
                pstmt.setObject(paramIdx, value);
            } else if (Short.class == parameterClass) {
                pstmt.setShort(paramIdx, (Short) value);
            } else if (BigDecimal.class == parameterClass) {
                pstmt.setBigDecimal(paramIdx, (BigDecimal) value);
            } else if (Double.class == parameterClass) {
                pstmt.setDouble(paramIdx, (Double) value);
            } else if (Float.class == parameterClass) {
                pstmt.setFloat(paramIdx, (Float) value);
            } else if (Number.class == parameterClass) {
                pstmt.setObject(paramIdx, value);
            } else if (value instanceof byte[]) {
                Blob blob = fluentConnection.createBlob();
                blob.setBytes(1, (byte[]) value);
                pstmt.setObject(paramIdx, blob);
            } else if (value instanceof Boolean) {
                pstmt.setObject(paramIdx, ((Boolean) value) ? (byte) 1 : (byte) 0);
            } else if (value instanceof AtomicInteger) {
                pstmt.setObject(paramIdx, ((AtomicInteger) value).get());
            } else if (value instanceof AtomicLong) {
                pstmt.setObject(paramIdx, ((AtomicLong) value).get());
            } else {
                pstmt.setObject(paramIdx, value);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 转换入参值及namedSql
     *
     * @param sqlKey   SQL的缓存 KEY
     * @param namedSql ":named"格式的SQL
     * @param paramMap 入参 MAP
     */
    private List<ParamInfo> buildParamInfoAndChangeNamedSql(Integer sqlKey, String namedSql, Map<String, Object> paramMap) {
        List<ParamInfo> paramInfos = getParamsFromCache(sqlKey);
        String cashSql = getSqlFromCache(sqlKey);
        if (StringKit.isNotEmpty(cashSql) && null != paramInfos) {//SQL已经转换过并缓存里有，则只转换新的入参值
            if (paramInfos != null && paramInfos.size() > 0) {
                for (ParamInfo p : paramInfos) {
                    Object v = paramMap.get(p.key);
                    v = StringKit.listOrArrayToLine(v);
                    p.vol = v;
                }
            }
        } else if (paramsNotEmpty(paramMap)) {//转换SQL语句及入参
            String backSql = new String(namedSql);
            paramInfos = new ArrayList<>();
            for (String key : paramMap.keySet()) {
                StringBuffer _tBuff = new StringBuffer(500);
                int keyStartPos = namedSql.indexOf(key);
                int keyEndPos = keyStartPos + key.length();
                int namedIdx = StringKit.countFind(backSql, ":", 0, backSql.indexOf(key)); //找出当前占位符在SQL的顺序
                _tBuff.append(namedSql, 0, keyStartPos);
                _tBuff.append("?");
                _tBuff.append(namedSql, keyEndPos, namedSql.length());
                namedSql = _tBuff.toString();
                //
                Object v = paramMap.get(key);
                // check sql hack
                checkSqlHack(namedSql, key, v);
                //change vol
                v = StringKit.listOrArrayToLine(v);
                paramInfos.add(new ParamInfo(key, v, namedIdx));
            }
            //把入参按位置排序
            List<ParamInfo> sortParamsList = paramInfos.stream().sorted(Comparator.comparing(ParamInfo::getNamedIdx)).collect(Collectors.toList());
            //写入缓存
            putSqlToCache(sqlKey, namedSql);
            putParamsToCache(sqlKey, sortParamsList);
        } else {
            putSqlToCache(sqlKey, namedSql);
        }
        return paramInfos;
    }

    private List<ParamInfo> buildParamInfo(String symbolsql, Object[] paramsArr) {
        List<ParamInfo> paramInfos = new ArrayList<>();
        if (null != paramsArr && paramsArr.length > 0) {
            int idx = 0;
            for (Object v : paramsArr) {
                checkSqlHack(symbolsql, "", v);
                v = StringKit.listOrArrayToLine(v);
                paramInfos.add(new ParamInfo("", v, ++idx));
            }
        }
        paramsArr = null;
        return paramInfos;
    }

    private void checkSqlHack(String symbolsql, String key, Object v) {
        if (check_sql_hack && v instanceof String) {
            if (QueryKit.isSQLHack((String) v, true)) {
                throw new SqlHackException(
                        "WARN FIND SQL HACK ==> key:" + key + " ,param:" + v + "\n" + " [SQL]: " + symbolsql);
            }
        }
    }


    private void putSqlToCache(Object sqlKey, String v) {
        CacheManager.buildCache(sqlKey, v, Cache.ONE_HOUR);
    }

    private void putParamsToCache(Object sqlKey, List<FluentSqlQuery.ParamInfo> v) {
        CacheManager.buildCache("p" + sqlKey, v, Cache.ONE_HOUR);
    }

    private String getSqlFromCache(Object key) {
        return CacheManager.getCacheVol(key);
    }


    private List<ParamInfo> getParamsFromCache(Object key) {
        return CacheManager.getCacheVol("p" + key);
    }

    private boolean paramsNotEmpty(Map<String, Object> params) {
        return (null != params && params.keySet().size() > 0);
    }

    private <T> T getFutureVal(CompletableFuture<T> future) {
        try {
            return future.get();
        } catch (InterruptedException e) {
            throw new FluentSqlException(e);
        } catch (ExecutionException e) {
            throw new FluentSqlException(e);
        }

    }

    private <T> List<T> getListVal(List<Map<String, Object>> rsList, Class<T> resultClass) {
        List<T> resultList = new ArrayList<>();
        if (!resultClass.isAssignableFrom(Map.class)) {
            if (rsList.size() > 0) {
                for (Map m : rsList) {
                    T o = ObjFactory.instanceOf(resultClass);
                    mapToPojo(m, o);
                    resultList.add(o);
                }
            }
        }
        return resultList;
    }

    private <T> T getObjVal(List<Map<String, Object>> rsList, Class<T> clz) {
        if (rsList.size() > 0) {
            T target = ObjFactory.instanceOf(clz);
            Map<String, Object> rsMap = rsList.get(0);
            if (QueryKit.isJavaNumberType(target)) {
                for (String key : rsMap.keySet()) {
                    return (T) rsMap.get(key);
                }
            }
            mapToPojo(rsMap, target);
            return target;
        }
        return null;
    }

    private void mapToPojo(Map source, Object target) {
        if (TableFieldSpell.UNDERLINE == fluentConnection.getTableFieldSpell()) {
            BeanMapUtils.builder()
                    .setFormatVal(true)//兼容性优先原则,默认做格式转换
                    .toCamel()
                    .c(source, target);
        }
        //
        else if (TableFieldSpell.CAMEL == fluentConnection.getTableFieldSpell()) {
            BeanMapUtils.builder()
                    .setFormatVal(true)//兼容性优先原则,默认做格式转换
                    .c(source, target);
        }
        //数据表的字段为全大写而JAVA对应的POJO为驼峰，转换就设定为fuzzy模式转换，速度比较慢，不推荐！
        else if (TableFieldSpell.UPCASE == fluentConnection.getTableFieldSpell()) {
            BeanMapUtils.fuzzy().c(source, target);
        }
    }

    private PreparedStatement buildPstmt(String sql, int readMode, int writeMode) {
        try {
            return fluentConnection.prepareStatement(sql, readMode, writeMode);
        } catch (SQLException e) {
            PrintUtil.error("SQL: " + sql);
            throw new FluentSqlException(e);
        }
    }

    private void closeStatement(Statement statement) {
        try {
            if (null != statement && !statement.isClosed()) {
                statement.close();
                statement = null;
            }
        } catch (SQLException e) {
            throw new FluentSqlException(e);
        }
    }

    public static class ParamInfo {

        private String key;
        private Object vol;
        private Integer namedIdx;  //占位符在sql的顺序位

        public ParamInfo(String key, Object vol, Integer namedIdx) {
            this.key = key;
            this.vol = vol;
            this.namedIdx = namedIdx;
        }

        public Integer getNamedIdx() {
            return namedIdx;
        }
    }

}
